package com.etc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.etc.pojo.ScoreDetailInfo;
import com.etc.utils.DBUtil;

public class ScoreDaoImpl implements ScoreDao {

	@Override
	public List<ScoreDetailInfo> queryScoreByStudentNo(String studentNo) {
		List<ScoreDetailInfo> list = new ArrayList<ScoreDetailInfo>();
		// 课程表和成绩表关联
		ResultSet rs = DBUtil.doQuery(
				"select tab_course.course_name,tab_course.course_score,tab_score.score from tab_score inner join tab_course on tab_score.course_no = tab_course.course_no where tab_score.student_no = ?",
				studentNo);
		try {
			while (rs.next()) {
				String courseName = rs.getString("course_name");
				float courseScore = rs.getFloat("course_score");
				float score = rs.getFloat("score");
				list.add(new ScoreDetailInfo(courseName, courseScore, score));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<ScoreDetailInfo> queryAllScore() {
		List<ScoreDetailInfo> list = new ArrayList<ScoreDetailInfo>();
		// 学生表 课程表 成绩表
		StringBuilder sql = new StringBuilder();
		sql.append("select").append("  tab_score.score_id").append(" ,tab_student.student_no")
				.append(" ,tab_student.student_name").append(" ,tab_course.course_name").append(" ,tab_score.score")
				.append(" from tab_score inner join tab_course on tab_score.course_no = tab_course.course_no")
				.append(" inner join tab_student on tab_student.student_no = tab_score.student_no");

		ResultSet rs = DBUtil.doQuery(sql.toString());
		try {
			while (rs.next()) {
				int scoreId = rs.getInt("score_id");
				String studentNo = rs.getString("student_no");
				String studentName = rs.getString("student_name");
				String courseName = rs.getString("course_name");
				float score = rs.getFloat("score");
				list.add(new ScoreDetailInfo(scoreId, studentNo, studentName, courseName, score));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<ScoreDetailInfo> queryByCondition(Map<String, Object> params) {
		List<ScoreDetailInfo> list = new ArrayList<ScoreDetailInfo>();
		// 学生表 课程表 成绩表
		StringBuilder sql = new StringBuilder();
		sql.append("select").append("  tab_score.score_id").append(" ,tab_student.student_no")
				.append(" ,tab_student.student_name").append(" ,tab_course.course_name").append(" ,tab_score.score")
				.append(" from tab_score inner join tab_course on tab_score.course_no = tab_course.course_no")
				.append(" inner join tab_student on tab_student.student_no = tab_score.student_no");
		ResultSet rs = null;
		// 情况1：按照学号
		if (!"".equals(params.get("studentNo")) && "".equals(params.get("courseNo"))) {

			sql.append(" where tab_student.student_no  = ?");
			rs = DBUtil.doQuery(sql.toString(), params.get("studentNo"));

		} else if ("".equals(params.get("studentNo")) && !"".equals(params.get("courseNo"))) {// 情况2：按照课程

			sql.append(" where tab_course.course_no  = ?");
			rs = DBUtil.doQuery(sql.toString(), params.get("courseNo"));

		} else if (!"".equals(params.get("studentNo")) && !"".equals(params.get("courseNo"))) {// 情况3：按照学号+课程

			sql.append(" where tab_course.course_no  = ?").append(" and   tab_student.student_no  = ?");
			rs = DBUtil.doQuery(sql.toString(), params.get("courseNo"), params.get("studentNo"));
			
		} else if ("".equals(params.get("studentNo")) && "".equals(params.get("courseNo"))) {// 情况4：两个都为空 查所有
			rs = DBUtil.doQuery(sql.toString());
		}

		try {
			while (rs.next()) {
				int scoreId = rs.getInt("score_id");
				String studentNo = rs.getString("student_no");
				String studentName = rs.getString("student_name");
				String courseName = rs.getString("course_name");
				float score = rs.getFloat("score");
				list.add(new ScoreDetailInfo(scoreId, studentNo, studentName, courseName, score));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int saveScore(String studentNo, int courseNo, float score) {
		// TODO Auto-generated method stub
		return DBUtil.doUpdate("insert into tab_score values(null,?,?,?,now())", studentNo,courseNo,score);
	}

}
